﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Yunlib.Common
{
    public class NpoiOffice2007Export
    {
        public static MemoryStream Export(DataTable exportTable, Dictionary<string, string> exportColumns)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = new XSSFWorkbook();

            //编辑工作薄当中内容
            ISheet sheet = workbook.CreateSheet();
            IRow headerRow = sheet.CreateRow(0);
            List<string> OriginalColumns = new List<string>();

            //重修而导出列头
            int columnIndex = 0;
            foreach (var key in exportColumns.Keys)
            {
                if (exportTable.Columns.Contains(key))
                {
                    headerRow.CreateCell(columnIndex++).SetCellValue(exportColumns[key]);

                    OriginalColumns.Add(key);
                }
            }

            // handling value.
            int rowIndex = 1;
            for (int r = 0, c = exportTable.Rows.Count; r < c; r++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex++);

                for (int i = 0, l = OriginalColumns.Count; i < l; i++)
                {
                    dataRow.CreateCell(i).SetCellValue(exportTable.Rows[r][OriginalColumns[i]].ToString());
                }
            }

            workbook.Write(ms);
            ms.Flush();
            headerRow = null;
            sheet = null;
            workbook = null;
            return ms;
        }

        public static void CreateExcel(string filePath, DataTable exportTable)
        {
            try
            {
                if (!File.Exists(filePath))
                    _CreateEmptyExportExcel(filePath, exportTable);

                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//读取流

                //POIFSFileSystem ps = new POIFSFileSystem(fs);//需using NPOI.POIFS.FileSystem;
                IWorkbook workbook = new XSSFWorkbook(fs);
                ISheet sheet = workbook.GetSheetAt(0);//获取工作表
                IRow row = sheet.GetRow(0); //得到表头
                FileStream fout = new FileStream(filePath, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);//写入流
                row = sheet.CreateRow((sheet.LastRowNum + 1));//在工作表中添加一行

                ICell cell1 = row.CreateCell(0);
                cell1.SetCellValue("测试数据");//赋值

                fout.Flush();
                workbook.Write(fout);//写入文件
                workbook = null;
                fout.Close();
            }
            catch (Exception ex)
            {
                LoggerHelper.Error("导出数据错误:{0}".FormatWith(ex.Message),ex);
                throw new Exception("导出数据错误"); ;
            }
        }

        static void _CreateEmptyExportExcel(string filePath, DataTable exportTable)
        {
            //创建内存流用于写入文件       
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = new HSSFWorkbook();   //创建Excel工作部   
            ISheet sheet = workbook.CreateSheet("导出数据");//创建工作表
            IRow headerRow = sheet.CreateRow(sheet.LastRowNum);//在工作表中添加一行

            //重修而导出列头
            for (int i = 0; i < exportTable.Columns.Count; i++)
                headerRow.CreateCell(i).SetCellValue(exportTable.Columns[i].ColumnName);

            workbook.Write(ms);//将Excel写入流
            ms.Flush();
            ms.Position = 0;

            FileStream dumpFile = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
            ms.WriteTo(dumpFile);//将流写入文件
        }
    }
}
